package com.season.seasonStudy.javase.j_008_database;



/**
 * 是什么？
 * 		数据库  后缀为 dbs
 * 		数据库文件  后缀为  dbf
 * 		日志文件    后缀为  ldf(记录每天都干了什么,可以通过日志文件进行恢复)
 * 	
 * 为什么？
 * 		数据库更适合存储东西！！！
 * 		优势:量大,可长久保存,可迅速找到；
 *			安全,共享！！！
 * 		劣势:需电,查找慢！
 * 用在哪？
 * 		数据控制:
 * 			存:处理冗余,完整性约束(真正正确的信息)
 * 			取:各种语言  SQL——>DDL  DCL  DQL  DML(各种标准！！)
 * 怎么做？
 * 		组成:
 * 		用表来表示组成！！！！
 * 	
 * 怎么用:
 * 	先搞清楚谁是一,谁是多！！就是一对多和多对一
 * 
 * 	一对多的情况:
 * 		书是多,图书类型是一！！就是把一的主键放到多的表里当多的表里当外键
 * 		就是另一个表会简化,那就让这个表做外表,他的主键当外键
 * 多对多的情况:
 * 		另建一个表！！实现一对多的关系！所以到最后都是一对多！！
 * 	
 * 
 *(A)没登陆之前的准备工作！！(用超级用户设置)
 * 		一开始用 超级用户进行登录:sys   角色:dba；
 * 	
 * 		解锁用户:alter user scott account unlock;
 * 		(这个可以随便设置密码,即使你忘了,你也可以用这个给改回来)
 * 		设置密码:alter user scott identified by 123;
 * 		后来开发用普通用户,(进过解锁的普通用户)
 * 		用户名:scott  密码:123；
 * (B)登陆！！		
 * 		登陆(方式一):sqlplus 用户名/密码    回车
 * 		登陆(方式二):sqlplus 回车    然后等待提示！！
 * 		登陆(方式三):用开始菜单里的sqlplus工具！！等待提示！
 * 
 * (C)操作
 * 		下边的为SQL语句:
 * 		show user;//显示当前用户！
 * 		exit//退出当前用户！
 * 		cls//用在住命令下！起到清屏的作用！
 */


/**
 * (01)oracle服务器由哪二部份组成？
 * 			实例(看不见)
 * 			数据库(看得见)
 * 
 * (02)SQL92/99标准有哪四大分类？
 * 
 * 		数据操纵语言
 * 		数据定义语言
 * 		数据控制语言
 * 		事务控制语言
 * 
 * (03)select *,ename from emp这样写对吗？
 * 		错误是*,ename不是一个层次的,*是所有,ename只是*下面的一个
 * 
 * (04)NVL(column,exchange)函数是什么意思？
 * 		如果column是null,那么返回exchange
 * 			select empno,nvl(ename,'who?') from emp;
 * 
 * (05)select sysdate from dual;
 * 		返回当前时间！
 * 
 * (06)select ename from emp where ename like '%\_%' escape '\'是什么意思？
 * 		escape将斜杠后边的符号还原他本来的意思！
 * 		不是我自己写的还是不懂,好傻逼啊
 * 
 * (07)如果判断null值？
 * 		select * from emp where comm is null;
 * 		select ename from emp where ename is null;	这里什么也不会显示
 * 		select ename from emp where ename is not null;	显示所有非空
 * 
 * (09)between .. and ..和in..是什么意思？
 * 		where empno between begin and end;
 * 				在几和几之间  
 * 		in(A,B)
 * 				是符合规则的都行,罗列所有可能
 * 
 * 
 * 
 * 
 * 		select * from emp;
 * 
 * 		select e.job,avg(sal),min(sal),max(sal) 
 * 			from emp e where job<>'CLERK' group by job ;
 * 
 * 		select e.job,avg(sal),min(sal),max(sal) 
 * 			from emp e group by job having job<>'CLERK' ;
 * 
 * 		select to_char(hiredate,'yyyy-mm-dd"那天是"day HH12:MI:SS AM') 
 * 			from emp where hiredate is not null;
 * 
 * 		select to_char(hiredate,'yyyy') ,avg(sal),min(sal),max(sal) 
 * 			from emp where job<>'CLERK' group by to_char(hiredate,'yyyy') ;
 * 
 * 		select to_char(hiredate,'yyyy') a,nvl(comm,0),ename from emp order by a;
 * 
 */

/**
 * SQL四种语言:DDL,DML,DCL,TCL
 * 
 * 	1.DDL(Data Definition Language)
 * 		数据库定义语言
 * 		statements are used to define the database structure or schema.
 * 
 * 		DDL是SQL语言的四大功能之一。
 * 		用于定义数据库的三级结构,包括外模式、概念模式、内模式及其相互之间的映像,
 * 		定义数据的完整性、安全控制等约束
 * 
 * 		DDL不需要commit.
 * 		
 * 		CREATE	创建
 * 		ALTER	修改
 * 		DROP	删除
 * 		TRUNCATE
 * 		COMMENT
 * 		RENAME	重命名
 * 
 * 	2.DML(Data Manipulation Language)
 * 		数据操纵语言
 * 		statements are used for managing data within schema objects.
 * 
 * 		由DBMS提供,用于让用户或程序员使用,实现对数据库中数据的操作。
 * 		DML分成交互型DML和嵌入型DML两类。
 * 		依据语言的级别,DML又可分成过程性DML和非过程性DML两种。
 * 		
 * 		需要commit.
 * 			
 * 		SELECT
 * 		INSERT
 * 		UPDATE
 * 		DELETE
 * 		MERGE
 * 		CALL
 * 		EXPLAIN PLAN
 * 		LOCK TABLE
 * 
 * 
 * 	3.DCL(Data Control Language)
 * 		数据控制语言  授权,角色控制等
 * 
 * 		GRANT 授权
 * 		REVOKE 取消授权
 * 
 * 	4.TCL(Transaction Control Language)
 * 		事务控制语言
 * 
 * 		SAVEPOINT 设置保存点
 * 		ROLLBACK  回滚
 * 		SET TRANSACTION
 * 
 * SQL主要分成四部分:
 * (1)数据定义。(SQL DDL)用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
 * (2)数据操纵。(SQL DML)数据操纵分成数据查询和数据更新两类。
 * 		数据更新又分成插入、删除、和修改三种操作。
 * (3)数据控制。包括对基本表和视图的授权,完整性规则的描述,事务控制等内容。
 * (4)嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则
 * 
 */


/**
 * ---------------------sql编写-具体思路----------------------------------------------------
接到一个查找任务,先考虑应该用什么方法达到这个功能,方法需要一个什么样的表,这个表中应该有什么数据！！！！
一般的方法都是纵向运算的。但运算前先过滤一遍！！这样能减少不必要的麻烦！！！下面介绍几个常用方法！！！


1、 查询出来的东西放在预查结果的地方(from)前方！！！！！

----------------------------------------(每一啥啥啥里有多少啥啥啥！！)-----------------------------
   
应用:每一个部门有几个人,每一个人选修了几门课,每一门课有几人,每一门课的平均分,每一门课的总分！！！

select dname,nvl((select count(*) from emp e where e.deptno=e1.deptno group by deptno),0) from dept e1
每个部门分别有几个人！！！
select student.sname ,
nvl((select count(*) from sc where student.sno=sc.sno group by sno ),0),
nvl((select avg(sc.score) from sc where student.sno=sc.sno group by sno ),0)
 from student --因为此时student.name是唯一的所以就可以用这样的表达式,把student.sno提前运算
--出来这样就只运行出来一行东西！！所以在这里多行函数都可以用！！


2、排名次方法
--------------------------------------(重复的排到一个名次里,或排到不同名次里)----------------------
(1)select 表字段,rank() over (order by 表字段)    from 表名

				     这个地方在他的范围内排序
(2)select t.*,rank() over (partition by sname order by score desc)

3、和in差不多的东西,但是可以判断了
--------------------------------------(in,exists,all,any)----------------------------------------

select *from dept where exists
             (select * from emp where dept.deptno=emp.deptno)
    
select *from dept where exists
             (select * from emp where dept.deptno=emp.deptno)        
             
--数字必须写在前边,           
select *from dept where 800<all
             (select sal from emp where dept.deptno=emp.deptno)
             and exists
             (select * from emp where dept.deptno=emp.deptno)
             
--任何一个大于800的           
select *from dept where 800<any
             (select sal from emp where dept.deptno=emp.deptno)
           
--条件是有关系就可以！！但必须是单列的数据！！多列的数据只能放在表的位置,而单列可以放在查询的位置,可以放在where从句里！！！
select ename,deptno,sal,(select avg(sal) from emp e where e.deptno=e1.deptno group by deptno)from emp e1

4、老方法新用途！！！



-----方法一,最基础的方法
cross join 是全都交叉相称一遍,这样数据还是比较少的！！
数据多的是left join或right join是把所有不同,不一样的都打出来的,这就得用on来限制一下！！

----方法二,想联系俩张表,直接在搜索的地方放上一张搜索结果,条件就写在这里边！！！但放在这得搜索结果必须是多句变一句函数！！
----方法三,搜索结果放在where从句里,on语句也可以放！！
----方法四,搜索结果放在from的地方把他当作一张表使用！！,,方法三同样适用于having从句里！！

------------------------------------(全部交叉一遍！！！！)--------------------------
select * from (select * from student cross join course)t left join sc on t.sno=sc.sno and t.cno=sc.cno where sc.sno is null
-----全部交叉一遍！！！






--查询部门信息,及部门人数:;::
-----先关联在聚合！！！！
select dname,loc,dept.deptno,count(empno) from dept
left join emp on dept.deptno=emp.deptno
group by dname,loc,dept.deptno
--3、先聚合再关联！！！！！！
select dname,loc,dept.deptno,nvl(renshu,0) from dept
left outer join ( 
select deptno,count(*) renshu from emp group by deptno)t 
on t.deptno=dept.deptno




--每个部门每个岗位的人数！！！！！！！！！交叉报表！！纵向变横向！！！###**************得一个一个判断！！！！！！

select deptno,job,count(*) from emp group by deptno,job

select job,count(*) from emp group by job

select deptno,
       sum(case when job='CLERK' then renshu else 0 end) CLERK,
       sum(case when job='SALESMAN' then renshu else 0 end) SALESMAN,
       sum(case when job='MANAGER' then renshu else 0 end) MANAGER,
        sum(case when job='PRESIDENT' then renshu else 0 end) PRESIDENT,
        sum(case when job='ANALYST' then renshu else 0 end) ANALYST
   from (select deptno,job,count(*) renshu from emp group by deptno,job) t
  group by deptno

 */



/**
//模糊查询  字符串
//select * from emp where like'%A%'      包含A的
//select * from emp where like'_A%'   第二个字母是A的
//select * from emp where like'_A'    一共两个字母    第二个字母是A的
//select * from emp where like'__A__' 五个字母  中间是A的
//select * from emp where job in('clerk','mag') 
//	select * from emp where hiredate between to_date("1922-1-1",'yyyy-mm-dd') and to_date("1923-1-1",'yyyy-mm-dd')

//	 主排序列  次排序
//	 select *from emp order by job desc,sal


//聚合


//	select count(*),job,max(mgr) from emp group by job;




//SQL 结构化查询语言
//
// DDL数据定义语言 创建结构:创建数据库,创建表对象 
//             Create,alter,drop
// DCL数据控制语言 分配 权限
//              grant,revoke
//----------------------------------------------
//对表中的数据进行处理 
// DML 数据操作语言 insert ,update , delete         
// DQL 数据查询语言 select
// 
// 插入语句 insert
//      insert into 表名 (列名,列名,...)
//      values  (值,值,...)
// 
// 注意事项:1 列名个数和值的个数一致,对应列的类型一致
//           2 自增列可以不写(sqlserver,mysql),
//             oracle自增用序列(sequence),
//             序列值:序列名.nextval
//           3 列允许为空,可以不用添加
//           4 注意约束:主键约束(主键列不重复)  
//                        check约束(自定义的)
//                        非空 (不能不填)
//                        外键约束(外键不能添加主键表的主键不存在问题)
//                      
//       insert into   emp(empno,ename )    
//              values (seq_emp.nextval,'aaa')
//             
//       部门 主键表             学生1  n
//           1   1  
//           部门号 主键                  成绩   1   1   90
//       员工 外键表             课程 n  1              1    2   98
//           n   1                                2    1   70
//           部门号 外键
//           
//           商品 1 n
//                       订单详情  1  1    
//           订单 n 1              1  2
//              用户号
//                1 n
//           用户
//                1 1
//    更新语句:
//                update  表名 
//                        set 列名=值,列名=值 ,...
//                where  条件
//                注意事项:1满足条件的行全部更新  
//                2 可选择部分列
//                3 跟新是列可以进行运算
//                4 注意约束
//                5 主键列不能修改
//          update emp set ename='lisi',sal=sal+100
//          where empno=8108
//          
//   删除语句
//          delete  from 表名  where  条件  
//             注意外键约束 
//-----------------------------------------------------------
//   查询
//          select
//                  列  "别 名",列,...     (显示列)
//          from 表名         
//          where 条件
//          group by 分组列,分组列
//          having  聚合函数条件
//          order by 列 desc , 列 asc
//条件:
//    比较运算符 =,<>,>,<,>=,<=
//    逻辑运算符 not 非 ,and 并且,or 或者
//      列名 > 值   and  列名< 值           
//      not(列名=值)  
// 等值范围判断  (数字,日期 ,文字)
//    列名 in(值1,值2,值3) <--------> 列 = 值1 or 列=值2 or 列=值3
// 值1和值2之间范围判断包含边界 (数字,日期)  
//    列名 begin 值1 and 值2  <--------> 列 >=值1 and 列 <=值2
// 模糊查询  (文字)
//    通配符的种类 % 0到多个任意字符 , _ 1个任意字符 
//    列名 like '通配符'
//          select * from emp
//          where  hiredate between to_date('1981-1-1','yyyy-mm-dd') 
//                              and to_date('1981-12-31','yyyy-mm-dd') 
//排序 
//   排序方式 
//   asc 升序(默认),desc 降序,紧跟列名不能跨越逗号
//   order by 列
//   主排序列,次要排序列
//   
// select * from emp
// order by job ,sal desc
// 
//聚合函数 (统计分析,表):count条数,sum某列和,avg某列平均,min某列最小值,max某列最大值
//   -- select ename, to_char(hiredate,'yyyy'),nvl(comm,0) from emp
//select count(*),sum(comm),avg(nvl(comm,0)) from emp
//    注意:1 count 对记录行来说的,其他对应具体列(数值列)
//          2 所有函数空行不记录在内
//          3 聚合函数旁边存在非聚合函数列,该列必须放入group by 中 
//          4 排序列,having 非聚合函数条件列 必须在select中列出来的列才能排序
//          5 非聚合函数条件放where中,聚合函数条件放having中
//select to_char(hiredate,'yyyy'),
//     min(sal+nvl(comm,0)),max(sal+nvl(comm,0)),avg(sal+nvl(comm,0)) from emp
//--where to_char(hiredate,'yyyy')='1981'
//group by to_char(hiredate,'yyyy')
//having count(*)>1
//
//    
//    select * from emp
//    
//         
// 规则:不区分大小写('值'例外)    
//       单引号表示字符串: '字符串'
//       逗号:分割列
//       能用列名的地方,就可使用函数,计算列
          
          

//select * from orders inner join users on orders.userid=users.id where users.username="aaa";
*/

/**
 * 



	create table copy_emp 
as 
select * from emp;

create table copy_copy_emp 
as 
select * from emp;



启动如下二个服务:【OracleServiceORCL(设置为手动启动)和OracleOraDb11g_home1TNSListener(设置为自动启动端口1521)】
类似于MySQL中的:use 数据库名,即use orcl;
即启动当前数据库名:orcl


-------------------------------------------------------------------------------------select


--------------------------------------------------------------------------------------------------------------------
show,column,set pagesize,distinct,,spool,spool off,@,NVL(null,10)
--------------------------------------------------------------------------------------------------------------------

查询emp表的所有内容,*号表示通配符,表示该表中的所有字段,但*号不能和具体字段一起使用
select * from emp;
或
select empno,ename,sal,deptno from emp;

查询emp表的员工编号,姓名,工资,部门号,列名,大小写不敏感,但提倡大写
select empno "编号",ename "姓名",sal "工资",deptNO "部门号" FROM Emp;

查询emp表的不重复的工作
select distinct job from emp;

查询员工的编号,姓名,月薪,年薪(月薪*12)
select empno,ename,sal,sal*12 "年薪" from emp;

查询员工的编号,姓名,入职时间,月薪,年薪,年收入(年薪+奖金)
select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+comm "年收入" from emp;
如果结果为null,在sqlplus客户端工具中,是不显示null这个值的

解决null的问题,使用NVL()函数,NVL(a,b):如果a是NULL,用b替代;如果a是非NULL,就不用b替代,直接返回a的值\
select NVL(null,10) from emp;结果有14行记录
select NVL(null,10) from dual;结果有1行记录
select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入" 
from emp;
注意:null与具体数字运算时,结果为null

使用列别名,查询员工的编号,姓名,月薪,年薪,年收入(年薪+奖金),AS大小写都可且可以省略AS,别名用双引号
select empno AS "编号",ename as "姓名",sal "月薪" 
from emp;
或
select empno AS 编号,ename as 姓名,sal 月薪 
from emp;
区别
select empno AS "编号",ename as 姓名,sal "月    薪" 
from emp;
不加双引号的别名不能有空格；加了双引号的别名可以有空格
要加只能加双引号,不能加单引号,因为在oracle中单引号表示字符串类型或者是日期类型
列名不能使用单引号,因为oracle认为单引号是字符串型或日期型

使用dual哑表或者伪表,使用字符串连接符号||,输出"hello world",在oracle中from是必须写的
select 'hello' || ' world' "结果" from dual;

使用sysdate,显示系统当前时间,在默认情况下,oracle只显示日期,而不显示时间,格式:26-4月-15
select sysdate from dual;

使用字符串连接符号||,显示如下格式信息:****的薪水是****美元
select ename || '的薪水是' || sal || '美元' 
from emp; 

使用spool命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件
spool e:/oracle-day01.sql;

使用spool off命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件,结束语句
spool off;

使用@命令,将硬盘文件e:/crm.sql,读到orcl实例中,并执行文件中的sql语句
@ e:/crm.sql; 

使用--符号,设置单行注释
--select * from emp;

使用/* * /符号,设置多行注释
/*
select
*
from 
emp;

总结:你学过的所有技术中,关于注释的符号
html:
css:
js:
xml:
jsp:
mysql:
oracle:
java://和

SQL语句的特点
1)是SQL92/99的ANSI官方标准,只要按照该标准来写,在任何的关系型数据库中都可以直接执行
2)SQL语句的关健字不能简写,例如:select,where,from
3)大小写不敏感,提倡大写 
4)能够对表数据进行增删改查操作
5)必须以分号结束
6)通常称做语句

SQLPLUS命令的特点
1)是oracle自带的一款工具,在该工具中执行的命令叫SQLPLUS命令
2)SQLPLUS工具的命令中的关健字可以简写,也可以不简写,例如:col ename for a10;
3)大小写不敏感,提倡大写
4)不能够对表数据进行增删改查操作,只能完成显示格式控制,例如:设置显示列宽,清屏,记录执行结果
5)可以不用分号结束,也可以用分号结束,个人提倡不管SQL或SQLPLUS,都以分号结束
6)通常称做命令,是SQLPLUS工具中的命令
注意:SQLPLUS命令是SQLPLUS工具中特有的语句

单引号出现的地方如下:
1)字符串型,例如:'hello' || ' world'
2)日期型,例如'25-4月-15'

双引号出现的地方如下:
1)列别名,例如:sal*12 "年 薪",或 sal*12 年薪,个人提倡用""双引号作列别名
            

-------------------------------------------------------------------------------------where



--------------------------------------------------------------------------------------------------------------------
+-* /,||,and or,not,<>,=,between and ,in,like(%,_),is null,escape,''
--------------------------------------------------------------------------------------------------------------------



查询emp表中20号部门的员工信息
select * from emp where deptno = 20;

查询姓名是SMITH的员工,字符串使用'',内容大小写敏感
select * from emp where ename = 'SMITH';
总结:你所学过的技术中,哪些是大小写敏感,哪些是大小写不敏感

查询1980年12月17日入职的员工,注意oracle默认日期格式(DD-MON-RR表示2位的年份)
select * from emp where hiredate = '17-12月-80';

查询工资大于1500的员工
select * from emp where sal > 1500;

查询工资不等于1500的员工【!=或<>】
select * from emp where sal <> 1500;

查询薪水在1300到1600之间的员工,包括1300和1600
select * from emp where (sal>=1300) and (sal<=1600);
或
select * from emp where sal between 1300 and 1600;

查询薪水不在1300到1600之间的员工,不包括1300和1600
select * from emp where sal NOT between 1300 and 1600;

查询入职时间在"1981-2月-20"到"1982-1月-23"之间的员工
select * from emp where hiredate between '20-2月-81' and '23-1月-82';
注意:
1)对于数值型,小数值在前,大数值在后
2)对于日期型,年长值在前,年小值在后

查询20号或30号部门的员工,例如:根据ID号,选中的员工,批量删除
select * from emp where (deptno=20) or (deptno=30);
或
select * from emp where deptno in (30,20);

查询不是20号或30号部门的员工
select * from emp where deptno NOT in (30,20);


like


查询姓名以大写字母S开头的员工,使用%表示0个,1个或多个字符
select * from emp where ename like 'S';
等价
select * from emp where ename = 'S';
select * from emp where ename like 'S%';

注意:
凡是精确查询用=符号
凡是不精确查询用like符号,我们通常叫模糊查询
 
查询姓名以大写字母N结束的员工
select * from emp where ename like '%N';

查询姓名第一个字母是T,最后一个字母是R的员工
select * from emp where ename like 'T%R';

查询姓名是4个字符的员工,且第二个字符是I,使用_只能表示1个字符,不能表示0个或多个字符
select * from emp where ename like '_I__';

插入一条姓名为'T_IM'的员工,薪水1200
insert into emp(empno,ename) values(1111,'T_IM');

查询员工姓名中含有'_'的员工,使用\转义符,让其后的字符回归本来意思【like '%\_%' escape '\'】
select * from emp where ename like '%\_%' escape '\';

escape是使\变为转义符的作用！

插入一个姓名叫'的员工
insert into emp(empno,ename) values(2222,'''');

插入一个姓名叫''的员工
insert into emp(empno,ename) values(2222,'''''');

查询所有员工信息,使用%或%%
select * from emp;
select * from emp where ename like '%';
select * from emp where ename like '%_%';

查询佣金为null的员工
select * from emp where comm is null;
注意:null不能参数=运算
      null能参数number/date/varchar2类型运算

总结判断为null的语句:::
html:
css:
js:
xml:
jsp:
mysql:
oracle:is,在oracle中等号=是精确查找的意思！in是在里边查找的意思！！包含的意思！！有多少也不怕！！
java:==

查询佣金为非null的员工
select * from emp where comm is not null;

查询无佣金且工资大于1500的员工
select * 
from emp 
where (comm is null) and (sal>1500); 

查询工资是1500或3000或5000的员工 
select * 
from emp 
where sal in (4000,10000,1500,3,300,3000,5000);

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式一,使用!=或<>)
select *
from emp
where (job='MANAGER') or (job<>'ANALYST');

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式二,使用not)
select *
from emp
where (job='MANAGER') or (not(job='ANALYST'));


-------------------------------------------------------排序------------------------------order by


--------------------------------------------------------------------------------------------------------------------
asc升序,desc降序,主排序字段(次排序),order by sal asc; 
--------------------------------------------------------------------------------------------------------------------



查询员工信息(编号,姓名,月薪,年薪),按月薪升序排序,默认升序,如果月薪相同,按oracle内置的校验规则排序
select empno,ename,sal,sal*12 
from emp 
order by sal asc; 

查询员工信息(编号,姓名,月薪,年薪),按月薪降序排序
select empno,ename,sal,sal*12 
from emp 
order by sal desc; 

查询员工信息,按入职日期降序排序,使用列名
select empno,ename,sal,hiredate,sal*12 "年薪" 
from emp
order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号(从1开始,在select子句中的列号)
列名:
select empno,ename,sal,hiredate,sal*12 "年薪" 
from emp
order by hiredate desc;

别名: 
select empno,ename,sal,hiredate,sal*12 "年薪" 
from emp
order by "年薪" desc;

表达式:
select empno,ename,sal,hiredate,sal*12 "年薪" 
from emp
order by sal*12 desc;

列号,从1开始:
select empno,ename,sal,hiredate,sal*12 "年薪" 
from emp
order by 5 desc;

查询员工信息,按佣金升序或降序排列,null值看成最大值
select * from emp order by comm desc;

查询员工信息,对有佣金的员工,按佣金降序排列,当order by 和 where 同时出现时,order by 在最后
select *
from emp
where comm is not null
order by comm desc;

查询员工信息,按工资降序排列,相同工资的员工再按入职时间降序排列
select *
from emp
order by sal desc,hiredate desc;

select *
from emp
order by sal desc,hiredate asc;
注意:只有当sal相同的情况下,hiredate排序才有作用

查询20号部门,且工资大于1500,按入职时间降序排列
select *
from emp
where (deptno=20) and (sal>1500)
order by hiredate desc;

select * from emp where deptno in (10,20,30,50,'a');

-------------------------------------------------------------------------------------单行函数

--------------------------------------------------------------------------------------------------------------------
to_char(hiredata,'yyyy'),NVL(null,10),count(*),sum(sal),avg(sal),min(hiredate),max(hiredate),

--------------------------------------------------------------------------------------------------------------------

解决null的问题,使用NVL()函数,NVL(a,b):如果a是NULL,用b替代;如果a是非NULL,就不用b替代,直接返回a的值\
select NVL(null,10) from emp;结果有14行记录
select NVL(null,10) from dual;结果有1行记录


聚合函数一共有5个:
count(*),sum(sal),avg(sal),min(hiredate),max(hiredate)
非聚合函数:
	非聚合的一定要放在group by里！！
to_char(hiredata,'yyyy'),
分组聚合
avg(sal),
聚合函数旁边都有个非聚合函数！！！
select job,avg(sal),min(sal),max(sal) from emp 
group by job;


单行函数:只有一个参数输入,只有一个结果输出
多行函数或分组函数:可有多个参数输入,只有一个结果输出		

测试lower/upper/initcap函数,使用dual哑表
select lower('www.BAIdu.COM') from dual;
select upper('www.BAIdu.COM') from dual;
select initcap('www.BAIdu.COM') from dual;

测试concat/substr函数,从1开始,表示字符,不论中英文
select concat('hello','你好') from dual;正确
select concat('hello','你好','世界') from dual;错误
select 'hello' || '你好' || '世界' from dual;正确
select concat('hello',concat('你好','世界')) from dual;正确
select substr('hello你好',5,3) from dual;
5表示从第几个字符开始算,第一个字符为1,中英文统一处理
3表示连续取几个字符

测试length/lengthb函数,编码方式为UTF8/GBK(赵君),一个中文占3/2个字节长度,一个英文一个字节
select length('hello你好') from dual; 
select lengthb('hello你好') from dual; 

测试instr/lpad/rpad函数,从左向右找第一次出现的位置,从1开始
select instr('helloworld','o') from dual;
注意:找不到返回0
      大小写敏感 
select LPAD('hello',10,'#') from dual;
select RPAD('hello',10,'#') from dual;

测试trim/replace函数
select trim(' ' from '  he  ll                ') from dual;
select replace('hello','l','L') from dual;

测试round/trunc/mod函数作用于数值型
select round(3.1415,3) from dual;
select trunc(3.1415,3) from dual;
select mod(10,3) from dual;

当前日期:sysdate = 26-4月-15

测试round作用于日期型(month)
select round(sysdate,'month') from dual;

测试round作用于日期型(year)
select round(sysdate,'year') from dual;

测试trunc作用于日期型(month)
select trunc(sysdate,'month') from dual;

测试trunc作用于日期型(year)
select trunc(sysdate,'year') from dual;

显示昨天,今天,明天的日期,日期类型 +- 数值 = 日期类型
select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄,日期-日期=数值,假设:一年以365天计算,一月以30天计算
select ename "姓名",round(sysdate-hiredate,0)/365 "天数" from emp;

使用months_between函数,精确计算到年底还有多少个月
select months_between('31-12月-15',sysdate) from dual;

使用months_between函数,以精确月形式显示员工工龄
select ename "姓名",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数,下个月今天是多少号
select add_months(sysdate,1) from dual;

测试add_months函数,上个月今天是多少号
select add_months(sysdate,-1) from dual;

测试next_day函数,从今天开始算,下一个星期三是多少号【中文平台】
select next_day(sysdate,'星期三') from dual;

测试next_day函数,从今天开始算,下下一个星期三是多少号【中文平台】
select next_day(next_day(sysdate,'星期三'),'星期三') from dual;

测试next_day函数,从今天开始算,下一个星期三的下一个星期日是多少号【中文平台】
select next_day(next_day(sysdate,'星期三'),'星期日') from dual;

测试last_day函数,本月最后一天是多少号
select last_day(sysdate) from dual;

测试last_day函数,本月倒数第二天是多少号
select last_day(sysdate)-1 from dual;

测试last_day函数,下一个月最后一天是多少号
select last_day(add_months(sysdate,1)) from dual;

测试last_day函数,上一个月最后一天是多少号
select last_day(add_months(sysdate,-1)) from dual;

注意:
1)日期-日期=天数
2)日期+-天数=日期


-------------------------------------------------------------------------------------三大类型转换


oracle中三大类型与隐式数据类型转换
(1)varchar2变长/char定长-->number,例如:'123'->123
(2)varchar2/char-->date,例如:'25-4月-15'->'25-4月-15'
(3)number---->varchar2/char,例如:123->'123'
(4)date------>varchar2/char,例如:'25-4月-15'->'25-4月-15'

oracle如何隐式转换:
1)=号二边的类型是否相同
2)如果=号二边的类型不同,尝试的去做转换
3)在转换时,要确保合法合理,否则转换会失败,例如:12月不会有32天,一年中不会有13月
 
查询1980年12月17日入职的员工(方式一:日期隐示式转换)
select * from emp where hiredate = '17-12月-80';

使用to_char(日期,'格"常量"式')函数将日期转成字符串,显示如下格式:2015 年 04 月 25 日 星期六
select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;

使用to_char(日期,'格式')函数将日期转成字符串,显示如格式:2015-04-25今天是星期六 15:15:15
select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;
或
select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:$1,234
select to_char(1234,'$9,999') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:￥1,234select to_char(1234,'$9,999') from dual;
select to_char(1234,'L9,999') from dual;

使用to_date('字符串','格式')函数,查询1980年12月17日入职的员工(方式二:日期显式转换)
select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
或
select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
或
select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

使用to_number('字符串')函数将字符串‘123’转成数字123
select to_number('123') from dual;


注意:
select '123' + 123 from dual;246
select '123' || 123 from dual;123123


-----------------------------------



使用NVL(a,b)通用函数,统计员工年收入,NVL()作用于任何类型,即(number/varchar2/date)
通用函数:参数类型可以是number或varchar2或date类型

使用NVL2(a,b,c)通用函数,如果a不为NULL,取b值,否则取c值,统计员工年收入 

使用NULLIF(a,b)通用函数,在类型一致的情况下,如果a与b相同,返回NULL,否则返回a,比较10和10.0是否相同

使用SQL99标准通用语法中的case表达式,将职位是分析员的,工资+1000；职位是经理的,工资+800；职位是其它的,工资+400
case 字段 
     when 条件 then 表达式1
     when 条件 then 表达式2
     else 表达式n
end 

使用oracle专用语法中的decode()函数,职位是分析员的,工资+1000；职位是经理的,工资+800；职位是其它的,工资+400
decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)

单引号出现的地方如下:
1)字符串,例如:'hello'
2)日期型,例如:'17-12月-80'
3)to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')

双引号出现的地方如下:
1)列别名,例如:select ename "姓 名" from emp
2)to_char/to_date(日期,'YYYY"年"MM"月"DD"日" HH24:MI:SS')

-----------------------------------------------------------------------------------------------一般语句的写法

select 列 别名,列 from 表
where 非整合函数条件
group by 分组条件
having 聚合函数作为条件
order by 排序列


------------------------------------------------------------------------------------case when用法
	
select product_id,product_type_id,
　　case
　　when product_type_id=1 then 'Book'
　　when product_type_id=2 then 'Video'
　　when product_type_id=3 then 'DVD'
　　when product_type_id=4 then 'CD'
　　else 'Magazine'
　　end
　　from products 

 */

public class DatabaseDemo {
	
	

}
